1. Technical Field
This disclosure generally relates to system recovery in a computer database system, and more specifically relates to journaling database changes using a bit map for zones defined in each page of large page indexes in a structured query language index.
2. Background Art
Computer databases typically contain data space entries, or records, plus indexes that provide ordered lists of the data space entries based on key values contained in the data space entries. When changes are made to the entries in a data space(s), the corresponding database indexes over the data space may need to be updated in order to keep the indexes synchronized with respect to the data space they cover. Often the changes to the database index(es) are made first, followed by the changes to the data space. This order of changes is chosen to allow any conditions that would prevent the updating of the database indexes to surface before a data space is changed. The attempt to insert a duplicate key into a unique index is one such condition. When the system terminates abnormally, the data spaces and the database indexes relating thereto may not be synchronized. Some transactions may have caused database index(es) to be updated, but the associated data space entries may not have been updated at the time the system terminated.
Journaling of transactions which cause a change in a database is a well known technique, and is described in detail in the following references: U.S. Pat. No. 4,819,156 to DeLorme et al., and U.S. Pat. No. 5,574,897 to Hermsmeier et al. These prior art approaches were developed when the size of the logical pages being logged as virgin images within indexes was rather modest. Current operating systems (such as i5/OS by International Business Machines Corporation (IBM)) provide customized logical page sizes for indexes that can vary from 4 k up to 512K bytes. Larger logical page sizes often improve query performance because they increase the locality of reference, reduce the number of off-page traversals, and reduce the total number of disk-to-memory transfers required in order to satisfy the query operation. However, this query improvement comes at a price and that price often affects run-time index maintenance overhead as well as an increase in high availability recovery time. Each time a key is added or removed from an index the surrounding software query language (SQL) index is placed at risk from loss and this at-risk condition is mitigated by logging/journaling the so-called virgin/before image of the entire logical leaf page (see the patents cited above). The larger the leaves of the index, the more overhead, the larger the main memory footprint, the more churn and the greater the disk traffic associated with such index logging. This puts system administrators with a dilemma to select between better query performance or increased index maintenance for high availability recovery.
The prior art algorithms for journaling indexes break down when large logical leaf page sizes are employed and the resulting performance suffers. Disk write traffic soars and gate contention duration rises as increasingly larger quantities of bytes are being managed. A trimmer approach is needed which doesn't flood the disk with so many bytes on behalf of the before/virgin images of SQL indexes.
Without a way to more efficiently journal the affected areas of these larger page sizes by using a smaller footprint, system administrators will continue to be forced to choose between better query performance and fast recovery from failures in a computer database.